#!/usr/bin/python
# fileencoding = utf-8

import xlrd
import sys
import os
import re

reload(sys)
sys.setdefaultencoding('utf-8')


def dump_excel(excel_file_name, db_table_name, with_head):
	excel_book = xlrd.open_workbook(excel_file_name)
	sheet = excel_book.sheet_by_index(0)
	if with_head:
		for value in sheet.row_values(0):
			if type(value) not in [str, unicode]:
				raise ValueError("invalid head field type %s" % value)
		print "DROP TABLE IF EXISTS `%s`;" % db_table_name
		print "CREATE TABLE `%s` (" % db_table_name
		print ",\n".join(["  `%s` TEXT" % value for value in sheet.row_values(0)])
		print ") DEFAULT CHARSET utf8mb4;"

	def escape_value(row_value):
		if type(row_value) in (str, unicode):
			return re.sub("'", "\\'", row_value)
		elif type(row_value) in (int, float, long):
			if int(row_value) == row_value:
				return int(row_value)
			elif long(row_value) == row_value:
				return long(row_value)
			else:
				return row_value
		else:
			raise ValueError("invalid row_value %s" % row_value)

	def rows():
		for row_index in range(1, sheet.nrows):
			yield sheet.row_values(row_index)

	sheet_row = rows()
	if sheet_row:
		print "INSERT INTO `%s` (%s)\n  VALUES" % (
				db_table_name,
				", ".join(["`%s`" % field_name for field_name in sheet.row_values(0)])
				)

	print ",\n".join(["(%s)" % (", ".join(["'%s'" % escape_value(row_value) for row_value in sheet_row]))
				for sheet_row in rows()]) +';'

def main():
	if len(sys.argv) < 3:
		print 'Usage: %s excel_file_name db_table_name [-h]' % sys.argv[0]
		exit()
	with_head = False
	for param in sys.argv:
		if param == '-h':
			with_head = True
			break
	excel_file_name = sys.argv[1]
	db_table_name = sys.argv[2]
	if os.path.exists(excel_file_name):
		dump_excel(excel_file_name, db_table_name, with_head)

if __name__ == "__main__":
	main()
